package mysql;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class TestDepEmp2 {
	
	private static Properties props = initProps();
	
	private static Connection conn = null;
	
	public static void main(String[] args) {
		
		String driverEspecific = getPropietat("rdbms.driver");
	
		try {
			Class.forName(driverEspecific).newInstance();
			conn = DriverManager.getConnection(getPropietat("rdbms.url"),
					getPropietat("rdbms.usuario"), getPropietat("rdbms.pwd"));
			
			
			String sql0 = "select * from empleado";
			Statement st0 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ResultSet res= st0.executeQuery(sql0);
			
			while (res.next()){
				
				if (res.getString("nom_emp").equals("juan")){
					res.updateInt("id_dep", 1);
					res.updateString("nom_emp", "joan");
				}
				res.updateRow();
			}
			
			
			int tipoResultado=ResultSet.TYPE_SCROLL_SENSITIVE;
			int tipoActualizacion=ResultSet.CONCUR_UPDATABLE;

			boolean esPosible=conn.getMetaData()
				.supportsResultSetConcurrency(tipoResultado, tipoActualizacion);			
			if (esPosible){
				System.out.println("aquest driver accepta TYPE_SCROLL_SENSITIVE");
			}
			else{
				System.out.println("aquest driver no accepta TYPE_SCROLL_SENSITIVE");
			}
//			String sql1 = "insert into departamento values (null,'bilbao')";
//			Statement st = conn.createStatement();
//			st.execute(sql1, Statement.RETURN_GENERATED_KEYS);			
//			ResultSet rs = st.getGeneratedKeys();
//			
//			rs.next();
//			int pkZar = rs.getInt(1);
//			
//			String sql2 = "insert into empleado values (null,'pepe', " + pkZar + ")";
//			st = conn.createStatement();
//			st.executeUpdate(sql2);
			
			
			String sql ="select * from departamento";
			Statement st = conn.createStatement();
			ResultSet res2=st.executeQuery(sql);
			List<Departamento>  dptos = new ArrayList<>();
			while (res2.next())
			{ 
				int id = res2.getInt("id_dep");
				String nombre = res2.getString("nom_dep");
				dptos.add(new Departamento(id,nombre));
			}
			
			for(Departamento dpto: dptos) {
				System.out.println(dpto);
			}
			System.out.println("************************************************");
			String sqlJoin = "select d.id_dep, nom_dep, id_emp, nom_emp "+ "from departamento d inner join empleado e on "+
			"e.id_dep = d.id_dep";
			
			ResultSet rs = st.executeQuery(sqlJoin);
			ResultSetMetaData rsmd = rs.getMetaData();
			
			for (int i = 1; i<=rsmd.getColumnCount();i++){
				System.out.println(rsmd.getColumnName(i)+" - ");
			}
			
			
			System.out.println("Programa finalizado.");
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException e) {
			
			e.printStackTrace();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}
		}

		

	}
	private static Properties initProps() {

		String nomPropertiesFileDb = 
				System.getProperty("propFileDbName");

		InputStream is = TestJDBC.class.getResourceAsStream("/"
				+ nomPropertiesFileDb);

		props = new Properties();
		try {
			props.load(is);
			return props;
		} catch (IOException e) {
			e.printStackTrace();
		}
		throw new RuntimeException(
				"Error cargando fichero de propiedades de la BD!");
	}
	private static String getPropietat(String propiedad) {
		return props.getProperty(propiedad);
	}

}
